package com.xzcs.util.excel;

import com.xzcs.util.common.DateUtils;
import com.xzcs.util.common.StringUtils;
import com.xzcs.util.db.BatchSql;
import com.xzcs.util.db.DBUtils;
import com.xzcs.util.db.ProcedureUtils;
import com.xzcs.util.web.RequestUtils;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.util.*;

public class ExcelUtils {
	public final Logger logger = LoggerFactory.getLogger(ExcelUtils.class);

	public Font createFont(Workbook wb){
		Font font = wb.createFont();
		font.setBold(true);
		font.setColor(HSSFColor.BLACK.index);
		return font;
	}

	public CellStyle createCellStyle(Workbook wb){
		Font font = this.createFont(wb);
		CellStyle cellStyle = wb.createCellStyle();
		cellStyle.setFont(font);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		return cellStyle;
	}

	/**
	 * 缺少样式信息的单元格
	 * @param row
	 * @param column
	 * @return
	 */
	public Cell createCell(Row row, int column) {
		Cell cell = row.createCell(column);
		//cell.setEncoding((short) 1); // 支持中文导出
		return cell;
	}

	public String getCellStringValue(Cell cell) {
		DecimalFormat df = new DecimalFormat();
		if (cell == null)
			return "";
		switch (cell.getCellType()) {
			case Cell.CELL_TYPE_STRING:
				return cell.getStringCellValue().trim();
			case Cell.CELL_TYPE_NUMERIC:
				// 判断是否是日期型的单元格
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					return DateUtils.date2String(cell.getDateCellValue(), "yyyy-MM-dd HH:mm:ss");
				} else {
					try {
						return df.parse(String.valueOf(cell.getNumericCellValue())).toString().trim();
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			default:
				return "";
		}
	}

	/**
	 * 导入Excel文件数据到数据库表中
	 * @param request
	 * @param file	MultipartFile类型的文件
	 * @param tempTablePrefix	临时表名前缀
	 * @param paramsStr		参数数组
	 * @param procName		存储过程名
	 * @param procInParams	存储过程参数
	 * @param startRow		从Excel第几行开始导入
	 * @param orderFlag		顺序标志，如果为true，临时表中增加show_order字段，遍历excel时插入数据，输出结果文件时用于排序
	 * @return
	 */
	public Map<String, Object> importExcelToDB(HttpServletRequest request, MultipartFile file, String tempTablePrefix,
							   String[] paramsStr, String procName, String[][] procInParams, int startRow,
							   boolean orderFlag, DBUtils db, String downloadPath) {
		Map<String, Object> result = new HashMap<String, Object>();
		Workbook wb = null;
		try {
			wb = WorkbookFactory.create(file.getInputStream());
		} catch (Exception ex) {
			result.put("result", -2);
			result.put("msg", "excel读取错误！");
			return result;
		}

		BatchSql batchSql = new BatchSql();
		// 临时表名
		String tempTableName = tempTablePrefix + DateUtils.getToday("MMddHHmmss") + Math.round(Math.random() * 1000);

		// 创建临时表的SQL语句
		String createSql = this.getCreateSql(tempTableName, paramsStr, orderFlag);
		batchSql.addBatch(createSql);

		// 插入临时表的SQL语句
		String insertSql = this.getInsertSql(tempTableName, paramsStr, orderFlag);

		// 读取Excel文件中的数据
		Iterator<Row> iter = wb.getSheetAt(0).rowIterator();
		if(!iter.hasNext()) {// 未能读取到任何数据
			result.put("result", -3);
			result.put("msg", "未读取到任何数据！");
			return result;
		}

		for (int i = 0; iter.hasNext(); i++) {
			Row row = (Row) iter.next();
			if (i < startRow) {
				continue;
			}
			// 顺序标志为true，则需要给show_order插入参数
			List<Object> paramsList = new ArrayList<Object>();
			for(String param : paramsStr) {
				String[] temp = param.split(",");
				Cell cell = row.getCell(Integer.parseInt(temp[2]));
				paramsList.add(getCellStringValue(cell));
			}
			if(orderFlag) {
				paramsList.add(i);
			}
			batchSql.addBatch(insertSql, paramsList.toArray());
		}

		int v_result = 0;	// 定义存储过程执行结果
		int exc = db.doInTransaction(batchSql);
		if (exc == 0) {
			result.put("result", 0);
			result.put("msg", "SQl语句执行失败！");
			return result;
		} else {
			// 调用存储过程
			v_result = this.callValidProcedure(procName, tempTableName, procInParams, db);
			String dir = "";
			result.put("result", v_result);
			// 导入失败，导出结果excel
			if (v_result != 1) {
				dir = RequestUtils.getFilePath(request, downloadPath) ;
				String to_file_name = this.exportErrorExcel(dir, tempTableName, paramsStr, db, orderFlag);
				result.put("msg", "数据导入失败！");
				result.put("fileName", to_file_name);
				return result;
			}
			result.put("msg", "数据导入成功！");
			// 删除临时表
			db.update("drop table " + tempTableName);
		}
		return result;
	}

	/**
	 * 创建临时表SQL
	 * @param tempTableName
	 * @param paramsStr
	 * @return
	 */
	public String getCreateSql(String tempTableName, String[] paramsStr, boolean orderFlag) {
		String createSql = "create table " + tempTableName + "(";
		StringBuffer field = new StringBuffer("");	// 字段串
		for(String param : paramsStr) {
			String[] temp = param.split(",");
			field.append(temp[1] + " varchar(1000),");
		}
		// 拼接创建的SQL语句
		if(orderFlag) {
			createSql = createSql + field.toString() + "show_order int)";
		} else {
			createSql = createSql + field.deleteCharAt(field.length() - 1) + ")";
		}
		logger.debug("插入临时表的SQL语句：" + createSql);
		return createSql;
	}

	/**
	 * 插入临时表的SQL语句
	 * @param tempTableName
	 * @param paramsStr
	 * @return
	 */
	public String getInsertSql(String tempTableName, String[] paramsStr, boolean orderFlag) {
		String insertSql = "";
		StringBuffer field = new StringBuffer("");	  // 字段串
		StringBuffer quesMark = new StringBuffer(""); // 占位符串
		for(String param : paramsStr) {
			String[] temp = param.split(",");
			field.append(temp[1] + ",");
			quesMark.append("?,");
		}
		// 顺序标志为true，则字段串加show_order字段，占位符串增加一个占位符
		if(orderFlag) {
			field.append("show_order");
			quesMark.append("?");
		} else {
			field.deleteCharAt(field.length() - 1); // 去掉字段串末尾的逗号
			quesMark.deleteCharAt(quesMark.length() - 1); // 去掉占位符串末尾的逗号
		}
		// 拼接插入的SQL语句
		insertSql = "insert into " + tempTableName + "(" + field.toString() + ") values(" + quesMark.toString() + ")";
		logger.debug("拼接插入的SQL语句：" + insertSql);
		return insertSql;
	}

	/**
	 * 调用存储过程验证导入的数据
	 * @param procName		存储过程名
	 * @param tempTableName 临时表名
	 * @param procInParams	存储过程参数（必须与存储过程中的参数顺序一致），v_table_name和v_result分别为临时表名和返回结果，
	 * 						在本方法中已经提供，procInParams中无需再加，存储过程中参数顺序分别为第一和最后。
	 * @param db
	 * @return 返回存储过程执行结果v_result
	 */
	public int callValidProcedure(String procName, String tempTableName, String[][] procInParams,
								  DBUtils db) {
		int v_result = -1;
		ProcedureUtils proc = db.getProcUtils(procName);
		proc.setVarcharParam("in_table");		 // 临时表名
		proc.setValue("in_table", tempTableName);

		for(int i = 0; i < procInParams.length; i++) {
			String[] procInParam = procInParams[i];
			proc.setVarcharParam(procInParam[0]);
			proc.setValue(procInParam[0], procInParam[1]);
		}
		proc.setVarcharOutParam("out_result"); // 存储过程返回结果

		try {
			Map<String, Object> procMap = proc.execute();
			v_result = Integer.parseInt(StringUtils.get(procMap, "out_result"));
		} catch (Exception e) {
			v_result = -4;
			logger.error("存储过程执行异常：" + e);
		}
		logger.debug("存储过程执行结果: " + v_result);
		return v_result;
	}

	/**
	 * 导入失败，导出excel结果文件
	 * @param dir 			导出路径
	 * @param tempTableName 临时表名
	 * @param paramsStr 	参数数组，另外，“导入失败原因”在本方法中固定为check_remark，在参数数组paramsStr中无需再加，且存储过程中需要向临时表中增加该字段。
	 * @param db
	 * @param orderFlag 	顺序标识
	 * @return 返回结果文件的文件名
	 */
	public String exportErrorExcel(String dir, String tempTableName, String[] paramsStr,
								   DBUtils db, boolean orderFlag) {
		// 定义结果文件Excel的标题
		String[][] excelParams = new String[paramsStr.length + 1][2];
		for(int i=0;i<paramsStr.length;i++) {
			String[] temp = paramsStr[i].split(",");
			excelParams[i] = new String[]{temp[0], temp[1]};
		}
		excelParams[paramsStr.length] = new String[]{"导入失败原因", "check_remark"};

		String sql = "select * from " + tempTableName;
		// 顺序标识为true，则按照show_order排序
		if(orderFlag) {
			sql += " order by show_order";
		}

		List<?> expList = db.queryForList(sql);
		Workbook wbExport = new XSSFWorkbook();
		Sheet sheet = wbExport.createSheet("importResult");
		Row row = sheet.createRow((short) 0);
		for (int i = 0; i < excelParams.length; i++) {
			Cell cell = createCell(row, i);
			cell.setCellStyle(createCellStyle(wbExport));
			cell.setCellValue(excelParams[i][0]);
		}
		for (int i = 0; i < expList.size(); i++) {
			row = sheet.createRow((short) i + 1);
			Map hash = (Map) expList.get(i);
			for (int j = 0; j < excelParams.length; j++) {
				createCell(row, j).setCellValue(StringUtils.notEmpty(hash.get(excelParams[j][1])));
			}
		}

		String to_file_name = tempTableName + ".xlsx";	// 结果文件名称
		File outFile = new File(dir, to_file_name);
		try {
			FileOutputStream outStream = new FileOutputStream(outFile);
			wbExport.write(outStream);
			outStream.close();
		} catch (Exception e) {
			logger.error("导出结果文件异常：" + e);
		}
		return to_file_name;
	}

	/**
	 * 校验Excel表头
	 * @param file 导入的文件
	 * @param titles 正确表头字符串数组
	 * @param sheet_index 表索引 从0开始
	 * @param row_index 行索引 从0开始
	 * @return
	 */
	public String checkExcelHead(MultipartFile file, String[] titles, int sheet_index, int row_index) {
		String message = "";
		if (file == null || file.getSize() == 0) {
			message = "导入错误：文件不存在或文件大小为0!";
			return message;
		}

		try {
			Workbook wb = WorkbookFactory.create(file.getInputStream());
			Sheet sheet = wb.getSheetAt(0);
			if (sheet == null) {
				message = "导入错误：未找到sheet！";
			} else {
				Row row = wb.getSheetAt(sheet_index).getRow(row_index);
				if (row == null) {
					message = "Excel标题列加载错误：没有标题列!";
					return message;
				}

				int cols = row.getPhysicalNumberOfCells();
				if (cols != titles.length) {
					message = "导入错误：导入的Excel必须为" + titles.length + "列！";
					return message;
				}

				for (int i = 0; i < cols; i++) {
					Cell cell = row.getCell((short) i);
					String cellValue = this.getCellStringValue(cell);
					if (!cellValue.equals(titles[i])) {
						message = "导入错误：Excel第" + this.getCellCode(i) + "列列名必须为“" + titles[i] + "”！";
						break;
					}
				}
			}
		} catch(Exception e) {
			e.printStackTrace();
			message = "导入错误：文件读取异常!";
		}
		return message;
	}

	public String getCellCode(int index) {
		String rows = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
		if (index / 25 < 1) {
			return String.valueOf(rows.charAt(index));
		} else {
			int cj = index / 26;
			if (cj == 0) {
				return String.valueOf(rows.charAt(index));
			}
			int mod = index % 26;
			return String.valueOf(rows.charAt(cj - 1))
					+ String.valueOf(rows.charAt(mod));
		}
	}

	/**
	 * 创建单元格样式
	 * @param workbook
	 * @param fillForegroundColor
	 * @param boldweight
	 */
	public CellStyle createCellStyle(Workbook workbook, short fillForegroundColor, boolean boldweight) {
		CellStyle style = workbook.createCellStyle();
		// 设置这些样式
		style.setFillForegroundColor(fillForegroundColor);
		style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		style.setBorderBottom(BorderStyle.THIN);//细边线
		style.setBorderLeft(BorderStyle.THIN);
		style.setBorderRight(BorderStyle.THIN);
		style.setBorderTop(BorderStyle.THIN);
		style.setAlignment(HorizontalAlignment.CENTER);
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		// 生成一个字体
		Font font = workbook.createFont();
		font.setColor(HSSFColor.BLACK.index);
		font.setFontHeightInPoints((short) 10);
		font.setBold(boldweight);
		style.setFont(font);
		return style;
	}

	public void exportDataToExcel(HttpServletRequest request, HttpServletResponse response,
								  DBUtils db, String sql, List<String> params,
								  String[][] headers, String excelName) {

		OutputStream out = null;
		try {
			out = response.getOutputStream();
			response.reset();
			response.setContentType("application/msexcel");
			response.setHeader("Content-disposition", "attachment;filename=" + java.net.URLEncoder.encode(excelName+".xlsx", "UTF-8"));
			response.setCharacterEncoding("UTF-8");

			// 声明一个工作薄
			Workbook workbook = new XSSFWorkbook();
			// 创建表头样式
			CellStyle style1 = createCellStyle(workbook, HSSFColor.YELLOW.index, true);
			// 创建数据单元格样式
			CellStyle style2 = createCellStyle(workbook, HSSFColor.WHITE.index, false);

			Drawing patriarch = null;// 画图的顶级管理器
			Sheet sheet = null;
			Row row = null;
			Cell cell = null;
			int sheetNum = 0;
			int rownumb = 0;

			Connection conn = db.getDataSource().getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			for(int i=0;i<params.size();i++){
				ps.setObject(i+1, params.get(i));
			}
			ResultSet rs = ps.executeQuery();
			int i = 0;
			while (rs.next()) {
				if (i % 30000 == 0) {// 超过30000就自动创建一个sheet，同时在新的sheet中创建表头
					rownumb = 0;
					sheetNum ++;
					sheet = workbook.createSheet("sheet"+sheetNum);
					sheet.setDefaultColumnWidth(16);

					// 创建表头
					row = sheet.createRow(0);
					row.setHeight((short)400);
					for (int j = 0; j < headers.length; j++) {
						cell = row.createCell(j);
						cell.setCellStyle(style1);
						cell.setCellValue(headers[j][0]);
					}
				}

				// 创建数据行
				row = sheet.createRow((short) rownumb + 1);
				BufferedImage bufferImg = null;
				String dataValue = "";
				for (int j = 0; j < headers.length; j++) {
					try {
						cell = row.createCell(j);
						cell.setCellStyle(style2);
						if (headers[j][2].equals("0")) {
							dataValue = StringUtils.notEmpty(rs.getObject((headers[j][1])));
						} else {
							dataValue = StringUtils.notEmpty(rs.getObject((headers[j][1])));
						}
						if("1".equals(headers[j][2])){// 图片格式的内容
							if(!"".equals(dataValue)) {// 图片格式的内容不为空
								try {
									patriarch = sheet.createDrawingPatriarch();
									String imgType = dataValue.substring(dataValue.indexOf(".") + 1); // 图片类型：jpg、png
									ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
									bufferImg = ImageIO.read(new File(dataValue));
									ImageIO.write(bufferImg, imgType, byteArrayOut);
									row.setHeight((short) 2024);
									sheet.setColumnWidth((short) j, 4196);
									HSSFClientAnchor anchor = new HSSFClientAnchor(1, 1, 1023, 255, (short) j,
											(short) i + 1, (short) j, (short) i + 1);
									anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_DO_RESIZE);
									patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(),
											this.getPictureType(imgType)));
								} catch (Exception e) {
									logger.debug(e.toString());
									cell.setCellValue("");
								}
							}
							else {
								cell.setCellValue(dataValue);
							}
						}
						else {// 其他格式的内容
							cell.setCellValue(dataValue);
						}
					}
					catch (Exception e) {
						cell.setCellValue("");// 出现异常，则单元格设置成空
						e.printStackTrace();
					}
				}
				rownumb ++;

				i++;
			}

			workbook.write(out);
			out.flush();
		} catch (Exception e) {
			logger.error("导出复杂类型的数据成Excel文件出现异常！" + e.toString());
			e.printStackTrace();
		}
		finally {
			if (out != null) {
				try {
					out.close();
				}
				catch (IOException ex) {
					ex.printStackTrace();
				}
			}
		}
	}

	/**
	 * 获取图片类型对应的数值
	 * @param imgType
	 * @return
	 */
	public int getPictureType(String imgType){
		int picType = 0;
		if("jpg".equalsIgnoreCase(imgType)){
			picType = Workbook.PICTURE_TYPE_JPEG;
		} else if ("png".equalsIgnoreCase(imgType)){
			picType = Workbook.PICTURE_TYPE_PNG;
		} else if ("dib".equalsIgnoreCase(imgType)){
			picType = Workbook.PICTURE_TYPE_DIB;
		} else {
			picType = Workbook.PICTURE_TYPE_JPEG;
		}
		return picType;
	}
}
